//
// Copyright (C) 2008 Jackson Harper (jackson@ximian.com)

// This program is free software; you can redistribute it and/or
// modify it under the terms of the GNU General Public License
// as published by the Free Software Foundation; either version 2
// of the License, or (at your option) any later version.

// This program is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.

// You should have received a copy of the GNU General Public License
// along with this program; if not, write to the Free Software
// Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
//
//

using System;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Configuration;
using System.Collections.Generic;

using log4net;
using MySql.Data.MySqlClient;

namespace Grr.Models
{
	public class Grurrah
	{
		private static readonly ILog log = LogManager.GetLogger(typeof(Grurrah));

		private long id;
		private string name;
		private string[] tags;
		private string text;
		private int rating;
		private string creator_name;
		private UserGrurrahStatus user_status;

		private Grurrah(long id, string name, string text, int rating, string creator_name, UserGrurrahStatus user_status, string[] tags)
		{
			this.id = id;
			this.name = name;
			this.text = text;
			this.creator_name = creator_name;
			this.rating = rating;
			this.user_status = user_status;
			this.tags = tags;
		}

		public long Id
		{
			get { return id; }
		}

		public string Name
		{
			get { return name; }
		}

		public string Text
		{
			get { return text; }
		}

		public int Rating
		{
			get { return rating; }
		}

		public string CreatorName {
			get { return creator_name; }
		}

		public UserGrurrahStatus UserStatus
		{
			get { return user_status; }
		}

		public string[] Tags
		{
			get { return tags; }
		}

		public static string UrlForGrurrah(string grurrah)
		{
			return String.Concat("~/grurrahs/", StringUtil.ToFriendlyUri(grurrah));
		}

		public static bool Create(string name, string tainted_text)
		{
			MembershipUser user = Membership.GetUser();
			long creator_id = user == null ? -1 : (long) user.ProviderUserKey;
			long grurrah_id = -1;

			using (MySqlConnection con = DbContext.OpenReadWriteConnection())
			{
				MySqlCommand cmd = new MySqlCommand("INSERT INTO Grurrahs (name, created_time, updated_time, creator, is_approved) VALUES (?name, NOW(), NOW(), ?creator_id, 0)", con);
				cmd.Parameters.Add("?name", MySqlDbType.VarChar, Validator.MaxGrurrahNameLength).Value = name;
				cmd.Parameters.Add("?creator_id", MySqlDbType.Int64).Value = creator_id;

				if (cmd.ExecuteNonQuery() != 1)
				{
					log.ErrorFormat("Unable to perform INSERT trying to Create new Grurrah of name: '{0}'.", name);
					return false;
				}

				cmd = new MySqlCommand("SELECT LAST_INSERT_ID()", con);
				grurrah_id = (long) cmd.ExecuteScalar();
			}

			return UpdateDescriptionText(grurrah_id, creator_id, tainted_text) != null;
		}

		public static Grurrah QueryForByName(string name)
		{
			return QueryForByName(name, null);
		}

		public static Grurrah QueryForByName(string name, MembershipUser user)
		{
			Grurrah res = null;
			using (MySqlConnection con = DbContext.OpenReadConnection())
			{
				MySqlCommand cmd = new MySqlCommand();
				string where_statement = "Grurrahs.name = ?name";
				long user_id = -1;

				if (user != null)
					user_id = (long)user.ProviderUserKey;

				cmd.Connection = con;
				cmd.Parameters.Add("?name", MySqlDbType.VarChar, Validator.MaxGrurrahNameLength).Value = name;
				cmd.Parameters.Add("?user_id", MySqlDbType.Int64).Value = user_id;

				res = RunQuery(cmd, where_statement);
			}

			return res;
		}

		public static Grurrah QueryForById(long id)
		{
			Grurrah res = null;
			using (MySqlConnection con = DbContext.OpenReadConnection())
			{
				MySqlCommand cmd = new MySqlCommand();
				string where_statement = "Grurrahs.id = ?id";

				cmd.Connection = con;
				cmd.Parameters.Add("?id", MySqlDbType.Int32).Value = id;

				res = RunQuery(cmd, where_statement);
			}

			return res;
		}


		// Returns the cleaned text
		public static string UpdateDescriptionText(long grurrah_id, string tainted_text)
		{
			MembershipUser user = Membership.GetUser();
			long creator_id = user == null ? -1 : (long)user.ProviderUserKey;
			return UpdateDescriptionText (grurrah_id, creator_id, tainted_text);
		}

		public static string UpdateDescriptionText(long grurrah_id, long creator_id, string tainted_text)
		{
			string desc = HtmlCleaner.CleanHtml(tainted_text);
			string creator_ip = HttpContext.Current.Request.UserHostAddress;
			// EntityFlags flags = SpamChecker.GetIsSpamFlags(desc);

			using (MySqlConnection con = DbContext.OpenReadWriteConnection())
			{
				MySqlCommand icmd = new MySqlCommand("INSERT into Revisions (grurrah_id, creator_id, creator_ip, created_time, flags, text) values (?grurrah_id, ?creator_id, INET_ATON(?creator_ip), NOW(), ?flags, ?text)", con);
				icmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				icmd.Parameters.Add("?creator_id", MySqlDbType.Int64).Value = creator_id;
				icmd.Parameters.Add("?creator_ip", MySqlDbType.String).Value = creator_ip;
				icmd.Parameters.Add("?flags", MySqlDbType.Int32).Value = 0;
				icmd.Parameters.Add("?text", MySqlDbType.Text).Value = desc;

				if (icmd.ExecuteNonQuery() != 1)
				{
					log.ErrorFormat("Unable to perform INSERT during UpdateDescriptionText for grurrah:{0}", grurrah_id);
					return desc;
				}

				MySqlCommand ucmd = new MySqlCommand("UPDATE Grurrahs SET revision_id = LAST_INSERT_ID() WHERE id = ?grurrah_id", con);
				ucmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;

				if (ucmd.ExecuteNonQuery() != 1)
				{
					log.ErrorFormat("Unable to perform UPDATE during UpdateDescriptionText for grurrah:{0}", grurrah_id);
					return desc;
				}
			}

			return desc;
		}

		public static void SetTags(long grurrah_id, List<string> tags, List<string> old_tags)
		{
			List<string> new_tags = new List<string>(tags.Count);

			foreach (string tag in tags)
				new_tags.Add(tag);

			foreach (string tag in new_tags)
			{
				if (old_tags.Contains(tag))
					continue;
				AddTag(grurrah_id, tag);
			}

			foreach (string tag in old_tags)
			{
				if (tag.Length < 1 || new_tags.Contains(tag))
					continue;
				RemoveTag(grurrah_id, tag);
			}
		}

		public static void AddTag(long grurrah_id, string tag_name)
		{
			Tag tag = Tag.QueryForByName(tag_name);

			if (tag == null)
				tag = Tag.Create(tag_name);

			if (tag == null)
			{
				log.ErrorFormat("Unable to create tag in AddTag call:  '{0}'", tag_name);
				return;
			}

			using (MySqlConnection rcon = DbContext.OpenReadWriteConnection())
			{
				MySqlCommand qcmd = new MySqlCommand("SELECT tag_id FROM tag_map WHERE grurrah_id = ?grurrah_id AND tag_id = ?tag_id", rcon);
				qcmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				qcmd.Parameters.Add("?tag_id", MySqlDbType.Int64).Value = tag.Id;

				MySqlDataReader reader = qcmd.ExecuteReader();
				if (reader.Read())
				{
					return;
				}

				using (MySqlConnection rwcon = DbContext.OpenReadWriteConnection())
				{
					MySqlCommand icmd = new MySqlCommand("INSERT INTO tag_map (grurrah_id, tag_id) VALUES (?grurrah_id, ?tag_id)", rwcon);
					icmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
					icmd.Parameters.Add("?tag_id", MySqlDbType.Int64).Value = tag.Id;

					icmd.ExecuteNonQuery();

					MySqlCommand ucmd = new MySqlCommand("UPDATE tag_data SET count = count + 1 WHERE tag_id = ?tag_id", rwcon);
					ucmd.Parameters.Add("?tag_id", MySqlDbType.Int64).Value = tag.Id;

					ucmd.ExecuteNonQuery();
				}
			}
		}

		public static void RemoveTag(long grurrah_id, string tag_name)
		{
			Tag tag = Tag.QueryForByName(tag_name);

			if (tag == null)
				return;

			using (MySqlConnection rwcon = DbContext.OpenReadWriteConnection())
			{
				MySqlCommand dcmd = new MySqlCommand("DELETE FROM tag_map WHERE grurrah_id = ?grurrah_id AND tag_id = ?tag_id", rwcon);
				dcmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				dcmd.Parameters.Add("?tag_id", MySqlDbType.Int64).Value = tag.Id;

				dcmd.ExecuteNonQuery();

				MySqlCommand ucmd = new MySqlCommand("UPDATE tag_data SET count = count - 1 WHERE tag_id = ?tag_id", rwcon);
				ucmd.Parameters.Add("?tag_id", MySqlDbType.Int64).Value = tag.Id;

				ucmd.ExecuteNonQuery();
			}
		}

		public static void SubmitRating(long grurrah_id, MembershipUser user, int rating)
		{
			using (MySqlConnection rwcon = DbContext.OpenReadWriteConnection())
			{
				MySqlCommand qcmd = new MySqlCommand("SELECT rating FROM user_grurrah_ratings WHERE user_id = ?user_id AND grurrah_id = ?grurrah_id", rwcon);
				qcmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				qcmd.Parameters.Add("?user_id", MySqlDbType.Int64).Value = (long)user.ProviderUserKey;

				object old = qcmd.ExecuteScalar ();
				int old_rating = old == null ? 0 : (int) old;
				int diff = rating - old_rating;
				int rating_count = old == null ? 1 : 0;

				if (old_rating == rating)
					return;

				MySqlCommand ucmd = new MySqlCommand("UPDATE Grurrahs SET rating_count = rating_count + ?rating_count, rating_sum = rating_sum + ?diff where id = ?grurrah_id", rwcon);
				ucmd.Parameters.Add("?diff", MySqlDbType.Int32).Value = diff;
				ucmd.Parameters.Add("?rating_count", MySqlDbType.Int32).Value = rating_count;
				ucmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				ucmd.Parameters.Add("?user_id", MySqlDbType.Int64).Value = (long) user.ProviderUserKey;

				if (ucmd.ExecuteNonQuery() != 1)
				{
					log.ErrorFormat("Unable to set rating_count in SubmitRating call for grurrah:{0}", grurrah_id);
					return;
				}

				if (old != null)
					ucmd = new MySqlCommand("UPDATE user_grurrah_ratings SET rating = ?rating WHERE user_id = ?user_id AND grurrah_id = ?grurrah_id", rwcon);
				else
					ucmd = new MySqlCommand("INSERT INTO user_grurrah_ratings (user_id, grurrah_id, rating) VALUES (?user_id, ?grurrah_id, ?rating)", rwcon);

				ucmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				ucmd.Parameters.Add("?user_id", MySqlDbType.Int64).Value = (long)user.ProviderUserKey;
				ucmd.Parameters.Add("?rating", MySqlDbType.Int32).Value = rating;

				if (ucmd.ExecuteNonQuery() != 1)
				{
					log.ErrorFormat("Unable to set user_grurrah_ratings in SubmitRating call for grurrah:{0}  doing query: {1}", grurrah_id, ucmd.CommandText);
					return;
				}
			}
		}

		public static void ClearRating(long grurrah_id, MembershipUser user)
		{
			using (MySqlConnection rwcon = DbContext.OpenReadWriteConnection())
			{
				MySqlCommand qcmd = new MySqlCommand("SELECT rating FROM user_grurrah_ratings WHERE user_id = ?user_id AND grurrah_id = ?grurrah_id", rwcon);
				qcmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				qcmd.Parameters.Add("?user_id", MySqlDbType.Int64).Value = (long)user.ProviderUserKey;

				object old_rating = qcmd.ExecuteScalar();
				if (old_rating == null)
					return;

				MySqlCommand ucmd = new MySqlCommand("UPDATE Grurrahs SET rating_count = rating_count - 1, rating_sum = rating_sum - ?old_rating where id = ?grurrah_id", rwcon);
				ucmd.Parameters.Add("?old_rating", MySqlDbType.Int32).Value = old_rating;
				ucmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				ucmd.Parameters.Add("?user_id", MySqlDbType.Int64).Value = (long)user.ProviderUserKey;

				if (ucmd.ExecuteNonQuery() != 1)
				{
					log.ErrorFormat("Unable to UPDATE rating_count in ClearRating for grurrah:	{0}", grurrah_id);
					return;
				}

				MySqlCommand dcmd = new MySqlCommand("DELETE FROM user_grurrah_ratings WHERE user_id = ?user_id AND grurrah_id = ?grurrah_id", rwcon);
				dcmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				dcmd.Parameters.Add("?user_id", MySqlDbType.Int64).Value = (long)user.ProviderUserKey;

				dcmd.ExecuteNonQuery();
				if (dcmd.ExecuteNonQuery() != 1)
				{
					log.ErrorFormat("Unable to DELETE from user_grurrah_ratings in ClearRating for grurrah:  {0}", grurrah_id);
					return;
				}
			}
		}

		public static void Approve(long grurrah_id)
		{
			using (MySqlConnection rwcon = DbContext.OpenReadWriteConnection())
			{
				MySqlCommand cmd = new MySqlCommand("UPDATE Grurrahs SET is_approved = 1 WHERE id = ?grurrah_id", rwcon);
				cmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;

				if (cmd.ExecuteNonQuery() != 1)
				{
					log.ErrorFormat("Unable to set is_approved on Grurrahs for grurrah: {0}", grurrah_id);
					return;
				}
			}
		}

		public static void Delete(long grurrah_id)
		{
			using (MySqlConnection rwcon = DbContext.OpenReadWriteConnection())
			{
				MySqlCommand cmd = new MySqlCommand("DELETE FROM Grurrahs WHERE id = ?grurrah_id", rwcon);
				cmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;

				if (cmd.ExecuteNonQuery() != 1)
				{
					log.ErrorFormat("Unable to DELETE Grurrah for grurrah: {0}", grurrah_id);
					return;
				}
			}
		}

		public static UserGrurrahStatus StatusForUser(long grurrah_id, MembershipUser user)
		{
			if (user == null)
				return UserGrurrahStatus.None;

			using (MySqlConnection rcon = DbContext.OpenReadConnection())
			{
				MySqlCommand cmd = new MySqlCommand("SELECT status FROM user_grurrah_status WHERE user_id = ?user_id AND grurrah_id = ?grurrah_id", rcon);
				cmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				cmd.Parameters.Add("?user_id", MySqlDbType.Int64).Value = user.ProviderUserKey;
				MySqlDataReader reader = cmd.ExecuteReader();

				if (!reader.Read () || reader[0] == DBNull.Value)
					return UserGrurrahStatus.None;

				return (UserGrurrahStatus)reader.GetInt16(0);
			}
		}

		public static void SetStatusForUser(long grurrah_id, MembershipUser user, UserGrurrahStatus status)
		{
			UserGrurrahStatus old_status = StatusForUser(grurrah_id, user);

			if (old_status == status)
				return;

			if (user == null)
				return;

			using (MySqlConnection rwcon = DbContext.OpenReadWriteConnection())
			{
				MySqlCommand cmd;
				if (old_status == UserGrurrahStatus.None)
					cmd = new MySqlCommand("INSERT INTO user_grurrah_status (user_id, grurrah_id, status) VALUES (?user_id, ?grurrah_id, ?status)", rwcon);
				else
					cmd = new MySqlCommand("UPDATE user_grurrah_status SET status = ?status WHERE user_id = ?user_id AND grurrah_id = ?grurrah_id", rwcon);

				if (status == UserGrurrahStatus.None)
					cmd = new MySqlCommand("DELETE FROM user_grurrah_status WHERE user_id = ?user_id AND grurrah_id =?grurrah_id", rwcon);
				
				cmd.Parameters.Add("?grurrah_id", MySqlDbType.Int64).Value = grurrah_id;
				cmd.Parameters.Add("?user_id", MySqlDbType.Int64).Value = user.ProviderUserKey;
				cmd.Parameters.Add("?status", MySqlDbType.Int16).Value = (int)status;

				if (cmd.ExecuteNonQuery() != 1)
				{
					log.ErrorFormat("Unable to update status for user:{0}  grurrah:{1}", user, grurrah_id);
				}
			}
		}

		private static readonly int GrurrahIdColumn = 0;
		private static readonly int GrurrahNameColumn = 1;
		private static readonly int RatingCountColumn = 2;
		private static readonly int RatingSumColumn = 3;
		private static readonly int CreatorColumn = 4;
		private static readonly int EntryTextColumn = 5;
		private static readonly int UserStatusColumn = 6;
		private static readonly int TagNameColumn = 7;


		private static readonly string SelectStatement = "SELECT Grurrahs.id, Grurrahs.name, Grurrahs.rating_count, Grurrahs.rating_sum, Grurrahs.creator, Revisions.text, user_grurrah_status.status, Tags.name FROM Grurrahs " +
				"RIGHT JOIN Revisions ON Grurrahs.revision_id = Revisions.id " +
				"LEFT JOIN tag_map ON Grurrahs.id = tag_map.grurrah_id " +
				"LEFT JOIN Tags on tag_map.tag_id = Tags.id " +
				"LEFT JOIN user_grurrah_status ON Grurrahs.id = user_grurrah_status.grurrah_id AND user_grurrah_status.user_id = ?user_id";

		private static readonly string GroupByStatement = "GROUP BY Grurrahs.id, Tags.name";

		private static Grurrah GrurrahFromReader(MySqlDataReader reader, bool include_tags, bool include_status)
		{
			long id = reader.GetInt64(GrurrahIdColumn);
			string name = reader.GetString(GrurrahNameColumn);
			string text = reader.GetString(EntryTextColumn);
			List<string> tags = new List<string>();
			int rating = 0;
			int rating_sum;
			int rating_count;
			long creator_id;
			UserGrurrahStatus status = UserGrurrahStatus.None;

			rating_sum = reader.GetInt32(RatingSumColumn);
			rating_count = reader.GetInt32(RatingCountColumn);
			creator_id = reader.GetInt64(CreatorColumn);

			if (rating_count > 0)
				rating = rating_sum / rating_count;

			if (include_status && reader[UserStatusColumn] != DBNull.Value)
			{
				status = (UserGrurrahStatus) reader.GetInt16(UserStatusColumn);
			}

			if (include_tags)
			{
				do
				{
					if (reader[TagNameColumn] == DBNull.Value)
						continue;
					string tag = reader.GetString(TagNameColumn);
					if (!tags.Contains(tag))
						tags.Add(tag);
				} while (reader.Read());
			}

			string creator_name;
			if (creator_id == -1)
				creator_name = "Anonymous";
			else
			{
				MembershipUser user = Membership.GetUser(creator_id);
				if (user == null)
					creator_name = "Anonymous";
				else
					creator_name = user.UserName;
			}

			return new Grurrah(id, name, text, rating, creator_name, status, tags.ToArray());
		}

		private static Grurrah RunQuery(MySqlCommand cmd, string where_statement)
		{
			cmd.CommandText = String.Format("{0} WHERE {1} {2}", SelectStatement, where_statement, GroupByStatement);
			MySqlDataReader reader = cmd.ExecuteReader();

			if (!reader.Read())
				return null;
			return GrurrahFromReader(reader, true, true);
		}

		private static readonly string SelectUnmoderatedStatement = "SELECT " +
			"Grurrahs.id, Grurrahs.name, Grurrahs.rating_count, Grurrahs.rating_sum, Grurrahs.creator, Grurrahs.user_status, Revisions.text FROM Grurrahs " +
			"JOIN Revisions ON Grurrahs.revision_id = Revisions.id";

		public static List<Grurrah> GetUnmoderatedGrurrahs()
		{
			List<Grurrah> grurrahs = new List<Grurrah>();
			using (MySqlConnection rcon = DbContext.OpenReadConnection())
			{
				string statement = String.Concat(SelectUnmoderatedStatement, " WHERE Grurrahs.is_approved = 0");
				MySqlCommand cmd = new MySqlCommand(statement, rcon);
				MySqlDataReader reader = cmd.ExecuteReader();

				while (reader.Read ())
				{
					Grurrah g = GrurrahFromReader(reader, false, false);
					grurrahs.Add(g);
				}
			}

			return grurrahs;
		}
	}
}

